package com.pagepub.expo.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

import com.pagepub.expo.entity.AppUser;
import com.pagepub.expo.entity.CorpPavilion;
import com.pagepub.expo.entity.IdName;
@Repository
public class CorpPavilionDao {
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate template;
	
	@Autowired
	@Qualifier("appSeqDao")
	private AppSeqDao appSeqDao;

	
	private static String tableName="Corp_Pavilion";
	
	private static String ID_NAME="SELECT id, name FROM "+tableName;

	private static String QUERY_ALL = "SELECT " +
			"id,name,keyword,imgsmallPath,imgPath," +
			"xWidth,yHeight,zZoom,url,imgWidth," +
			"imgHeight,parentId,imgFull,imgUp,imgNight,"+
			"posName,isSpecial" +
			" FROM "+tableName;

	private static String QUERY_ALL_COUNT = "SELECT COUNT(*) FROM "+  tableName;
	
	private static String INSERT_SQL = "INSERT  INTO "+tableName+" (" +
			"id,name,keyword,imgsmallPath,imgPath," +
			"xWidth,yHeight,zZoom,url,imgWidth," +
			"imgHeight,parentId,imgFull,imgUp,imgNight," +
			"posName,isSpecial)VALUES(" +
			":id,:name,:keyword,:imgsmallPath,:imgPath," +
			":xWidth,:yHeight,:zZoom,:url,:imgWidth," +
			":imgHeight,:parentId,:imgFull,:imgUp,:imgNight," +
			":posName,:isSpecial)";

	private static String DELETE_SQL = "DELETE FROM "+ tableName +" WHERE id=?";

	private static String UPDATE_SQL = "UPDATE "+ tableName+" SET " +
			"name=:name,keyword=:keyword,imgsmallPath=:imgsmallPath,imgPath=:imgPath," +
			"xWidth=:xWidth,yHeight=:yHeight,zZoom=:zZoom,url=:url,imgWidth=:imgWidth," +
			"imgHeight=:imgHeight,parentId=:parentId,imgFull=:imgFull,imgUp=:imgUp,imgNight=:imgNight,"+
			"posName=:posName,isSpecial=:isSpecial"+
			" WHERE id=:id";

	private final ParameterizedRowMapper<CorpPavilion> mapper = new ParameterizedRowMapper<CorpPavilion>() {
		public CorpPavilion mapRow(ResultSet rs, int rowNum) throws SQLException {
			CorpPavilion bean = new CorpPavilion();
			bean.setId(rs.getLong("id"));
			bean.setName(rs.getString("name"));
			bean.setKeyword(rs.getString("keyword"));
			bean.setImgsmallPath(rs.getString("imgsmallPath"));
			bean.setImgPath(rs.getString("imgPath"));
			bean.setxWidth(rs.getShort("xWidth"));
			bean.setyHeight(rs.getShort("yHeight"));
			bean.setzZoom(rs.getShort("zZoom"));
			bean.setUrl(rs.getString("url"));
			bean.setImgWidth(rs.getShort("imgWidth"));
			bean.setImgHeight(rs.getShort("imgheight"));
			bean.setParentId(rs.getShort("parentId"));
			bean.setImgFull(rs.getString("imgFull"));
			bean.setImgUp(rs.getString("imgUp"));
			bean.setImgNight(rs.getString("imgNight"));
			bean.setPosName(rs.getString("posName"));
			bean.setIsSpecial(rs.getInt("isSpecial"));
			return bean;
		}
	};

	public List<CorpPavilion> findAll() {
		return template.query(QUERY_ALL, mapper);
	}

	public CorpPavilion findById(Long id) {
		List<CorpPavilion> appUsers = template.query(QUERY_ALL + " WHERE id=?",
				new Object[] { id }, mapper);
		if (appUsers.size() == 0) {
			return null;
		} else
			return appUsers.get(0);
	}
	

	
	public void insert(CorpPavilion bean) {
		if(bean.getId()==null){
			bean.setId(new Long(appSeqDao.getSequence("app_user_seq")));
		}		
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(INSERT_SQL, ps);

	}

	public void delete(Long id) {
		template.update(DELETE_SQL, id);
	}

	public void update(CorpPavilion bean) {
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(UPDATE_SQL, ps);
	}
	
	public int getCount(Map<String, String> param){
		int count = template.queryForInt(QUERY_ALL_COUNT);
		return count;
	}
	
	public List findByPage(int currentPage,int pageSize,Map<String, String> param){
		List lists = template.query(QUERY_ALL + " limit ?,?",
				new Object[] { currentPage, pageSize}, mapper);
		return lists;
		
	}
	
	public List findByCorpIdPage(String corpId,int currentPage,int pageSize,Map<String, String> param){
		List lists = template.query(QUERY_ALL + " where corpId=?  limit ?,?",
				new Object[] {corpId,currentPage, pageSize}, mapper);
		return lists;
		
	}
	
	public List<IdName> findIdNames(){
		List lists = template.query(ID_NAME, IdName.idNameMapper);
		return lists;
	}
	
}
